#include "stdafx.h"
#include "AnswerDatabase.h"
#include "QuestionLibrary.h"
#include "LoginHelper.h"
#include "Logger.h"

#include "Poco\File.h"
#include "Poco\Exception.h"
#include "Poco\Format.h"
#include "Poco\Timezone.h"
#include "Poco\DateTimeFormatter.h"
#include "Poco\Data\Statement.h"
#include "Poco\Data\RecordSet.h"
#include "Poco\Data\Binding.h"
#include "Poco\Data\SQLite\Connector.h"

#include <assert.h>
#include <sstream>

namespace ExamSystem
{
    using Poco::DateTime;
    using Poco::Timezone;
    using Poco::Exception;
    using Poco::Data::now;
    using Poco::Data::into;
    using Poco::Data::range;
    using Poco::Data::use;
    using Poco::Data::Statement;
    using Poco::Data::RecordSet;

    CAnswerDatabase::AnswerLoader CAnswerDatabase::ms_answerLoaders[QuestionTypeCount] = {
        &CAnswerDatabase::LoadChoiceAnswer,
        &CAnswerDatabase::LoadFillingAnswer,
        &CAnswerDatabase::LoadTypingAnswer,
        &CAnswerDatabase::LoadQNAAnswer,
        &CAnswerDatabase::LoadOfficeAnswer,
        &CAnswerDatabase::LoadCodingAnswer,
        &CAnswerDatabase::LoadHtmlAnswer,
        &CAnswerDatabase::LoadPhotoshopAnswer
    };

    CAnswerDatabase::AnswerSaver CAnswerDatabase::ms_answerSavers[QuestionTypeCount] = {
        &CAnswerDatabase::SaveChoiceAnswer,
        &CAnswerDatabase::SaveFillingAnswer,
        &CAnswerDatabase::SaveTypingAnswer,
        &CAnswerDatabase::SaveQNAAnswer,
        &CAnswerDatabase::SaveOfficeAnswer,
        &CAnswerDatabase::SaveCodingAnswer,
        &CAnswerDatabase::SaveHtmlAnswer,
        &CAnswerDatabase::SavePhotoshopAnswer
    };

    const std::string CAnswerDatabase::ms_answerTables[QuestionTypeCount + 2] = {
        "choice", "filling", "typing", "qna", "office", "coding", "html", "photoshop", "students", "status"
    };

    CAnswerDatabase::CAnswerDatabase(void)
        : m_flagInitialized(false)
    {

    }

    CAnswerDatabase::~CAnswerDatabase(void)
    {

    }

    bool CAnswerDatabase::Initialize(const std::string &databaseFile)
    {
        if (m_flagInitialized && databaseFile == m_databaseFile)
            return true;

        //m_flagInitialized = false;

        Poco::File file(databaseFile);
        if (!file.exists())
        {
            systemLogger.error("Database " + databaseFile + " doesn't exist.");
            return false;
        }
        m_databaseFile = databaseFile;

        try
        {
            // register SQLite connector
            Poco::Data::SQLite::Connector::registerConnector();

            // create a session
            m_session = new Session("SQLite", m_databaseFile);
            if (!m_session->isConnected())
                throw Poco::Exception("Failed to open " + m_databaseFile);

            // check if there are all tables
            std::vector<std::string> names;
            (*m_session) << "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",
                into(names),
                now;

            for (int i = 0; i < QuestionTypeCount + 2; i++)
            {
                if (std::find(names.begin(), names.end(), ms_answerTables[i]) == names.end())
                    throw Poco::Exception(m_databaseFile + " is not an answer database.");
            }

            m_flagInitialized = true;
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            m_session = NULL;
            m_databaseFile.clear();
            m_flagInitialized = false;
            return false;
        }
    }

    bool CAnswerDatabase::CreateDatabase(const std::string &databaseFile, ExamInformation &info)
    {
        Poco::File file(databaseFile);
        if (file.exists())
            systemLogger.warning("Database " + databaseFile + " exists.");
        m_databaseFile = databaseFile;

        try
        {
            // register SQLite connector
            Poco::Data::SQLite::Connector::registerConnector();

            // create a session
            m_session = new Session("SQLite", m_databaseFile);
            if (!m_session->isConnected())
                throw Poco::Exception("Failed to open " + m_databaseFile);


            // create tables
            Session &session = *m_session;

            // delete all tables
            for (int i = 0; i < QuestionTypeCount + 2; i++)
                session << Poco::format("DROP TABLE IF EXISTS %s", ms_answerTables[i]), now;


            // choice
            session << "CREATE TABLE "
                "choice("
                    //"idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "idx CHAR(20) PRIMARY KEY, "
                    "number CHAR(10) NOT NULL, "
                    "id INTEGER NOT NULL, "
                    "score FLOAT NOT NULL, "
                    "answer SHORT" 
                    ")", now;
            m_tableColumns[typeChoice].clear();
            m_tableColumns[typeChoice].push_back("idx");
            m_tableColumns[typeChoice].push_back("number");
            m_tableColumns[typeChoice].push_back("id");
            m_tableColumns[typeChoice].push_back("score");
            m_tableColumns[typeChoice].push_back("answer");
            // filling
            session << "CREATE TABLE "
                "filling("
                    //"idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "idx CHAR(20) PRIMARY KEY, "
                    "number CHAR(10) NOT NULL, "
                    "id INTEGER NOT NULL, "
                    "score FLOAT NOT NULL, "
                    "answer NTEXT "
                    ")", now;
            m_tableColumns[typeFilling].clear();
            m_tableColumns[typeFilling].push_back("idx");
            m_tableColumns[typeFilling].push_back("number");
            m_tableColumns[typeFilling].push_back("id");
            m_tableColumns[typeFilling].push_back("score");
            m_tableColumns[typeFilling].push_back("answer");
            // typing
            session << "CREATE TABLE "
                "typing("
                    //"idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "idx CHAR(20) PRIMARY KEY, "
                    "number CHAR(10) NOT NULL, "
                    "id INTEGER NOT NULL, "
                    "score FLOAT NOT NULL, "
                    "answer NTEXT "
                    ")", now;
            m_tableColumns[typeTyping].clear();
            m_tableColumns[typeTyping].push_back("idx");
            m_tableColumns[typeTyping].push_back("number");
            m_tableColumns[typeTyping].push_back("id");
            m_tableColumns[typeTyping].push_back("score");
            m_tableColumns[typeTyping].push_back("answer");
            // typing
            session << "CREATE TABLE "
                "qna("
                    //"idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "idx CHAR(20) PRIMARY KEY, "
                    "number CHAR(10) NOT NULL, "
                    "id INTEGER NOT NULL, "
                    "score FLOAT NOT NULL, "
                    "answer NTEXT "
                    ")", now;
            m_tableColumns[typeQnA].clear();
            m_tableColumns[typeQnA].push_back("idx");
            m_tableColumns[typeQnA].push_back("number");
            m_tableColumns[typeQnA].push_back("id");
            m_tableColumns[typeQnA].push_back("score");
            m_tableColumns[typeQnA].push_back("answer");
            // office
            session << "CREATE TABLE "
                "office("
                    //"idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "idx CHAR(20) PRIMARY KEY, "
                    "number CHAR(10) NOT NULL, "
                    "id INTEGER NOT NULL, "
                    "score FLOAT NOT NULL, "
                    //"answer NTEXT, "
                    "file NTEXT " // base64 encoded
                    ")", now;
            m_tableColumns[typeOffice].clear();
            m_tableColumns[typeOffice].push_back("idx");
            m_tableColumns[typeOffice].push_back("number");
            m_tableColumns[typeOffice].push_back("id");
            m_tableColumns[typeOffice].push_back("score");
            //m_tableColumns[typeOffice].push_back("answer");
            m_tableColumns[typeOffice].push_back("file");
            // coding
            session << "CREATE TABLE "
                "coding("
                    //"idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "idx CHAR(20) PRIMARY KEY, "
                    "number CHAR(10) NOT NULL, "
                    "id INTEGER NOT NULL, "
                    "score FLOAT NOT NULL, "
                    //"answer NTEXT,"  // input and output files
                    "file NTEXT " // base64 encoded
                    ")", now;
            m_tableColumns[typeCoding].clear();
            m_tableColumns[typeCoding].push_back("idx");
            m_tableColumns[typeCoding].push_back("number");
            m_tableColumns[typeCoding].push_back("id");
            m_tableColumns[typeCoding].push_back("score");
            //m_tableColumns[typeCoding].push_back("answer");
            m_tableColumns[typeCoding].push_back("file");
            // html
            session << "CREATE TABLE "
                "html("
                    //"idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "idx CHAR(20) PRIMARY KEY, "
                    "number CHAR(10) NOT NULL, "
                    "id INTEGER NOT NULL, "
                    "score FLOAT NOT NULL, "
                    //"answer NTEXT, "
                    "file NTEXT" // base64 encoded
                    ")", now;
            m_tableColumns[typeHtml].clear();
            m_tableColumns[typeHtml].push_back("idx");
            m_tableColumns[typeHtml].push_back("number");
            m_tableColumns[typeHtml].push_back("id");
            m_tableColumns[typeHtml].push_back("score");
            //m_tableColumns[typeHtml].push_back("answer");
            m_tableColumns[typeHtml].push_back("file");
            // photoshop
            session << "CREATE TABLE "
                "photoshop("
                    //"idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "idx CHAR(20) PRIMARY KEY, "
                    "number CHAR(10) NOT NULL, "
                    "id INTEGER NOT NULL, "
                    "score FLOAT NOT NULL, "
                    "file NTEXT " // base64 encoded
                    ")", now;
            m_tableColumns[typePhotoshop].clear();
            m_tableColumns[typePhotoshop].push_back("idx");
            m_tableColumns[typePhotoshop].push_back("number");
            m_tableColumns[typePhotoshop].push_back("id");
            m_tableColumns[typePhotoshop].push_back("score");
            m_tableColumns[typePhotoshop].push_back("file");
            // student
            session << "CREATE TABLE "
                "students("
                    //"idx INTEGER PRIMARY KEY AUTOINCREMENT, "
                    "number CHAR(10) PRIMARY KEY, "
                    "name NVARCHAR NOT NULL,"
                    "score FLOAT NOT NULL"
                    ")", now;
            m_tableColumns[QuestionTypeCount].clear();
            m_tableColumns[QuestionTypeCount].push_back("idx");
            m_tableColumns[QuestionTypeCount].push_back("number");
            m_tableColumns[QuestionTypeCount].push_back("name");
            m_tableColumns[QuestionTypeCount].push_back("score");
            // status
            session << "CREATE TABLE "
                "status("
                    "field CHAR(15) PRIMARY KEY, "
                    "value VARCHAR"
                    ")", now;

            SaveExamInformation(info);

            m_flagInitialized = true;
            // update status
            //UpdateStatus(-1);
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::MergeDatabase(const std::string &fileName)
    {
        if (!m_session)
            return false;

        Poco::File file(fileName);
        if (!file.exists())
        {
            systemLogger.error("Database " + fileName + " doesn't exist.");
            return false;
        }

        try
        {
            systemLogger.information("Merging database " + m_databaseFile + " and " + fileName);
            Session &session = *m_session;

            // attach new database to current one
            session << Poco::format("ATTACH '%s' AS db2", fileName), now;

            // check database version
            std::string identityThis;
            std::string identityOther;
            session << "SELECT value FROM status WHERE field='identity'", into(identityThis), now;
            session << "SELECT value FROM db2.status WHERE field='identity'", into(identityOther), now;
            if (identityThis != identityOther)
                throw Poco::Exception("Identity check error.");

            // merge all tables by type
            for (int i = 0; i <= QuestionTypeCount; i++)
            {
                std::string str = "INSERT OR REPLACE INTO ";
                str += ms_answerTables[i];
                str += " SELECT * FROM db2." + ms_answerTables[i];
                //str += Poco::format(
                //    " SELECT idx + 1 + (SELECT MAX(idx) FROM %[0]s) - (SELECT MIN(idx) FROM db2.%[0]s)"
                //    , ms_answerTables[i]);

                //for (size_t j = 1; j < m_tableColumns[i].size(); j++)
                //    str += "," + m_tableColumns[i][j];

                //str += " FROM db2." + ms_answerTables[i];

                session << str, now;
            }

            session << "DETACH db2", now;
            //return UpdateStatus(-1);
            systemLogger.information("Merging succeeded.");
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            try 
            {
                *m_session << "DETACH db2", now;
            }
            catch (...)
            {
            }
            return false;
        }

        return false;
    }

    bool CAnswerDatabase::LoadExamInformation(ExamInformation &info)
    {
        if (!m_session)
            return false;

        try
        {
            Session &session = *m_session;
            std::string value;
            // identity
            session << "SELECT value FROM status WHERE field='identity'"
                , into(info.examIdentity)
                , now;
            // exam date and time
            session << "SELECT value FROM status WHERE field='datetime'"
                , into(info.examDateTime)
                , now;
            // how we generate questions
            session << "SELECT value FROM status WHERE field='generation'"
                , into(value)
                , now;
            info.examQuestionGenerationMethod = (value == "random") ? 
                ExamInformation::Random : ExamInformation::Specified;

            // status
            session << "SELECT value FROM status WHERE field='status'"
                , into(value)
                , now;

            if (value == "preparing")
                info.examStatus = ExamInformation::Preparing;
            else if (value == "processing")
                info.examStatus = ExamInformation::Processing;
            else
                info.examStatus = ExamInformation::Finished;
            
            // total time and rest time
            std::stringstream sstr;
            session << "SELECT value FROM status WHERE field='totalTime'"
                , into(value)
                , now;

            sstr.str(value);
            sstr >> info.examTotalTime;

            session << "SELECT value FROM status WHERE field='startTime'"
                , into(value)
                , now;

            sstr.str(value);
            sstr >> info.examStartTime;

            info.examEndTime = info.examStartTime + info.examTotalTime;
            info.examRestTime = info.examEndTime - (unsigned int)time(NULL);

            // specified questions
            if (info.examQuestionGenerationMethod == ExamInformation::Random)
            {
                for (int i = 0; i < QuestionTypeCount; i++)
                    info.examQuestions[i].clear();
            }
            else
            {
                for (int i = 0; i < QuestionTypeCount; i++)
                {
                    info.examQuestions[i].clear();
                    session << Poco::format("SELECT value FROM status WHERE field='%s'"
                        , ms_answerTables[i])
                        , into(value)
                        , now;

                    std::stringstream sstr(value);
                    double coef = 1.0;
                    sstr >> coef;
                    info.examQuestionCoefs[i] = coef;

                    int s = 0;
                    sstr >> s;
                    info.examQuestions[i].resize(s);
                    for (int j = 0; j < s; j++)
                        sstr >> info.examQuestions[i][j];
                }
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::SaveExamInformation(ExamInformation &info)
    {      
        if (!m_session)
            return false;

        try
        {
            Statement insert(*m_session);
            std::string key;
            std::string value;
            insert << "INSERT OR REPLACE INTO status VALUES(?, ?)", use(key), use(value);

            key = "identity";
            value = info.examIdentity;
            insert.execute();

            key = "datetime";
            value = info.examDateTime;
            insert.execute();

            key = "generation";
            value = info.examQuestionGenerationMethod == ExamInformation::Random ? "random" : "specified";
            insert.execute();

            key = "status";
            switch (info.examStatus)
            {
            case ExamInformation::Preparing:
                value = "preparing";
                break;
            case ExamInformation::Processing:
                value = "processing";
                break;
            case ExamInformation::Finished:
                value = "finished";
                break;
            }
            insert.execute();

            key = "startTime";
            value = Poco::format("%u", info.examStartTime);
            insert.execute();

            key = "totalTime";
            value = Poco::format("%u", info.examTotalTime);
            insert.execute();

            std::stringstream sstr;
            for (int i = 0; i < QuestionTypeCount; i++)
            {
                key = ms_answerTables[i];
                sstr.str("");
                sstr.clear();
                sstr << info.examQuestionCoefs[i] << " ";

                sstr << info.examQuestions[i].size() << " ";
                for (size_t j = 0; j < info.examQuestions[i].size(); j++)
                    sstr << info.examQuestions[i][j] << " ";

                value = sstr.str();
                insert.execute();
            }
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }

    }

    bool CAnswerDatabase::UpdateStatus(unsigned int nMask)
    {
        if (!m_session)
            return false;

        try
        {
            Session &session = *m_session;

            // TODO: implement it
            return false;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadStudents(std::vector<StudentInfo> &students, std::vector<double> &scores)
    {
        if (!m_session)
            return false;

        try
        {
            students.clear();
            scores.clear();

            Session &session = *m_session;
            std::vector<std::string> numbers;
            std::vector<std::string> names;
            session << "SELECT number,name,score FROM students"
                , into(numbers)
                , into(names)
                , into(scores)
                , now;

            students.resize(numbers.size());
            for (size_t i = 0; i < numbers.size(); i++)
            {
                students[i].studentNumber = numbers[i];
                students[i].studentName = names[i];
            };

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadQuestions(const StudentInfo &student, QuestionType questionType, std::vector<int> &questionIDs)
    {
        if (!m_session)
            return false;

        try
        {
            questionIDs.clear();
            Session &session = *m_session;
            session << Poco::format("SELECT DISTINCT id FROM %s WHERE number='%s' ORDER BY id"
                , ms_answerTables[questionType]
                , student.studentNumber)
                , into(questionIDs)
                , now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadQuestions(QuestionType questionType, std::vector<int> &questionIDs)
    {
        if (!m_session)
            return false;

        try
        {
            questionIDs.clear();
            Session &session = *m_session;
            session << Poco::format("SELECT DISTINCT id FROM %s ORDER BY id"
                , ms_answerTables[questionType])
                , into(questionIDs)
                , now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadQuestions(
        const StudentInfo &student,
        QuestionType questionType,
        std::vector<int> &questionIDs, 
        std::vector<double> &scores)
    {
        if (!m_session)
            return false;

        try
        {
            questionIDs.clear();
            scores.clear();
            Session &session = *m_session;
            session << Poco::format("SELECT DISTINCT id,score FROM %s WHERE number='%s' ORDER BY id"
                , ms_answerTables[questionType]
                , student.studentNumber)
                , into(questionIDs)
                , into(scores)
                , now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadAnswer(const StudentInfo &student, CAnswer *answer)
    {
        if (!m_session)
            return false;

        if (!answer)
            return false;

        return (this->*ms_answerLoaders[answer->Type()])(student, answer);
    }

    bool CAnswerDatabase::SaveAnswer(const StudentInfo &student, CAnswer *answer)
    {
        if (!m_session)
            return false;

        if (!answer)
            return false;

        return (this->*ms_answerSavers[answer->Type()])(student, answer);
    }

    bool CAnswerDatabase::LoadChoiceAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CChoiceAnswer &a = *static_cast<CChoiceAnswer *>(answer);
            session << Poco::format("SELECT score,answer FROM %s WHERE number='%s' AND id=%d"
                , ms_answerTables[a.Type()]
                , student.studentNumber
                , a.questionID)
                , into(a.answerScore)
                , into(a.answerChoice)
                , range(0, 1)
                , now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadFillingAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CFillingAnswer &a = *static_cast<CFillingAnswer *>(answer);
            std::string answerBlanks;
            session << Poco::format("SELECT score,answer FROM %s WHERE number='%s' AND id=%d"
                , ms_answerTables[a.Type()]
                , student.studentNumber
                , a.questionID)
                , into(a.answerScore)
                , into(answerBlanks)
                , range(0, 1)
                , now;

            CQuestionLibrary::SeparateString(answerBlanks, a.answerBlanks);

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadTypingAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CTypingAnswer &a = *static_cast<CTypingAnswer *>(answer);
            session << Poco::format("SELECT score,answer FROM %s WHERE number='%s' AND id=%d"
                , ms_answerTables[a.Type()]
                , student.studentNumber
                , a.questionID)
                , into(a.answerScore)
                , into(a.answerTyping)
                , range(0, 1)
                , now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadQNAAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CQNAAnswer &a = *static_cast<CQNAAnswer *>(answer);
            session << Poco::format("SELECT score,answer FROM %s WHERE number='%s' AND id=%d"
                , ms_answerTables[a.Type()]
                , student.studentNumber
                , a.questionID)
                , into(a.answerScore)
                , into(a.answerText)
                , range(0, 1)
                , now;

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadOfficeAnswer(const StudentInfo &student, CAnswer *answer)
    {
        return LoadFiledAnswer(student, answer);
    }

    bool CAnswerDatabase::LoadCodingAnswer(const StudentInfo &student, CAnswer *answer)
    {
        return LoadFiledAnswer(student, answer);
    }

    bool CAnswerDatabase::LoadHtmlAnswer(const StudentInfo &student, CAnswer *answer)
    {
        return LoadFiledAnswer(student, answer);
    }

    bool CAnswerDatabase::LoadPhotoshopAnswer(const StudentInfo &student, CAnswer *answer)
    {
        return LoadFiledAnswer(student, answer);
    }
        
    bool CAnswerDatabase::LoadFiledAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CFiledAnswer &a = *dynamic_cast<CFiledAnswer *>(answer);
            std::string fileString;
            session << Poco::format("SELECT score,file FROM %s WHERE number='%s' AND id=%d"
                , ms_answerTables[a.Type()]
                , student.studentNumber
                , a.questionID)
                , into(a.answerScore)
                , into(fileString)
                , range(0, 1)
                , now;

            a.answerFile.SetEncodedString(fileString);

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::LoadPropertyAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CPropertyAnswer &a = *dynamic_cast<CPropertyAnswer *>(answer);
            std::string fileString;
            std::string property;
            session << Poco::format("SELECT score,answer,file FROM %s WHERE number='%s' AND id=%d"
                , ms_answerTables[a.Type()]
                , student.studentNumber
                , a.questionID)
                , into(a.answerScore)
                , into(property)
                , into(fileString)
                , range(0, 1)
                , now;

            a.answerFile.SetEncodedString(fileString);

            std::vector<std::string> properties;
            CQuestionLibrary::SeparateString(property, properties);
            size_t count = properties.size() / 2;
            a.answerProperties.clear();
            a.answerValues.clear();
            for (size_t i = 0; i < count; i++)
            {
                a.answerProperties.push_back(properties[i]);
                a.answerValues.push_back(properties[i + count]);
            }

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        } 
   }

    inline std::string GenerateIndex(const std::string &number, int id)
    {
        return Poco::format("%s_%d", number, id);
    }

    bool CAnswerDatabase::CheckAnswerExist(const StudentInfo &student, CAnswer *answer, std::string &idx, double &score)
    {
        Statement check(*m_session);
        check << Poco::format("SELECT idx,score FROM %s WHERE number='%s' AND id=%d",
            ms_answerTables[answer->Type()], student.studentNumber, answer->questionID),
            into(idx), into(score), range(0, 1);

        return check.execute() > 0;
    }

    void CAnswerDatabase::UpdateStudentScore(const StudentInfo &student, double oldScore, double newScore)
    {
        // insert/update students
        Session &session = *m_session;
        Statement checkName(session);
        int nameIdx = -1;
        double totalScore = 0;
        checkName << Poco::format("SELECT score FROM students WHERE number='%s' AND name='%s'",
            student.studentNumber, student.studentName), 
            into(totalScore), range(0, 1);

        if (checkName.execute())
        {
            if (oldScore != newScore)
            {
                session << Poco::format("UPDATE students SET score=%f WHERE number='%s'",
                    totalScore - oldScore + newScore, student.studentNumber),
                    now;
            }
        }
        else
        {
            session << "INSERT INTO students VALUES(?, ?, ?)",
                use(student.studentNumber),
                use(student.studentName),
                use(newScore),
                now;
        }
    }

    bool CAnswerDatabase::SaveChoiceAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CChoiceAnswer &a = *static_cast<CChoiceAnswer *>(answer);
            std::string idx;
            double score = 0;
            if (CheckAnswerExist(student, answer, idx, score))    // the item exists, this is an update
            {
                session << Poco::format("UPDATE %s SET answer=%d,score=%f WHERE idx='%s'",
                    ms_answerTables[a.Type()], a.answerChoice, a.answerScore, idx),
                    now;
            }
            else    // the item doesn't exists, this is an insertion
            {
                session << Poco::format("INSERT INTO %s VALUES(?, ?, ?, ?, ?)",
                    ms_answerTables[a.Type()]),
                    use(GenerateIndex(student.studentNumber, a.questionID)),
                    use(student.studentNumber),
                    use(a.questionID),
                    use(a.answerScore),
                    use(a.answerChoice),
                    now;
            }

            UpdateStudentScore(student, score, a.answerScore);

            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::SaveFillingAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CFillingAnswer &a = *static_cast<CFillingAnswer *>(answer);
            std::string idx;
            double score = 0;
            if (CheckAnswerExist(student, answer, idx, score))    // the item exists, this is an update
            {
                session << Poco::format("UPDATE %s SET answer='%s',score=%f WHERE idx='%s'",
                    ms_answerTables[a.Type()], 
                    CQuestionLibrary::AssembleString(a.answerBlanks),
                    a.answerScore, 
                    idx),
                    now;
            }
            else    // the item doesn't exists, this is an insertion
            {
                session << Poco::format("INSERT INTO %s VALUES(?, ?, ?, ?, ?)",
                    ms_answerTables[a.Type()]),
                    use(GenerateIndex(student.studentNumber, a.questionID)),
                    use(student.studentNumber),
                    use(a.questionID),
                    use(a.answerScore),
                    use(CQuestionLibrary::AssembleString(a.answerBlanks)),
                    now;
            }

            UpdateStudentScore(student, score, a.answerScore);
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::SaveTypingAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CTypingAnswer &a = *static_cast<CTypingAnswer *>(answer);
            std::string idx;
            double score = 0;
            if (CheckAnswerExist(student, answer, idx, score))    // the item exists, this is an update
            {
                session << Poco::format("UPDATE %s SET answer='%s',score=%f WHERE idx='%s'",
                    ms_answerTables[a.Type()], 
                    a.answerTyping,
                    a.answerScore, 
                    idx),
                    now;
            }
            else    // the item doesn't exists, this is an insertion
            {
                session << Poco::format("INSERT INTO %s VALUES(?, ?, ?, ?, ?)",
                    ms_answerTables[a.Type()]),
                    use(GenerateIndex(student.studentNumber, a.questionID)),
                    use(student.studentNumber),
                    use(a.questionID),
                    use(a.answerScore),
                    use(a.answerTyping),
                    now;
            }

            UpdateStudentScore(student, score, a.answerScore);
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::SaveQNAAnswer(const StudentInfo &student, CAnswer *answer)
    {
        // TODO: implement it
        try
        {
            Session &session = *m_session;
            CQNAAnswer &a = *static_cast<CQNAAnswer *>(answer);
            std::string idx;
            double score = 0;
            if (CheckAnswerExist(student, answer, idx, score))    // the item exists, this is an update
            {
                session << Poco::format("UPDATE %s SET answer='%s',score=%f WHERE idx='%s'",
                    ms_answerTables[a.Type()], 
                    a.answerText,
                    a.answerScore, 
                    idx),
                    now;
            }
            else    // the item doesn't exists, this is an insertion
            {
                session << Poco::format("INSERT INTO %s VALUES(?, ?, ?, ?, ?)",
                    ms_answerTables[a.Type()]),
                    use(GenerateIndex(student.studentNumber, a.questionID)),
                    use(student.studentNumber),
                    use(a.questionID),
                    use(a.answerScore),
                    use(a.answerText),
                    now;
            }

            UpdateStudentScore(student, score, a.answerScore);
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::SaveOfficeAnswer(const StudentInfo &student, CAnswer *answer)
    {
        return SaveFiledAnswer(student, answer);
    }

    bool CAnswerDatabase::SaveCodingAnswer(const StudentInfo &student, CAnswer *answer)
    {
        return SaveFiledAnswer(student, answer);
    }

    bool CAnswerDatabase::SaveHtmlAnswer(const StudentInfo &student, CAnswer *answer)
    {
        return SaveFiledAnswer(student, answer);
    }

    bool CAnswerDatabase::SavePhotoshopAnswer(const StudentInfo &student, CAnswer *answer)
    {
        return SaveFiledAnswer(student, answer);
    }

    bool CAnswerDatabase::SaveFiledAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CFiledAnswer &a = *dynamic_cast<CFiledAnswer *>(answer);
            std::string idx;
            double score = 0;
            if (CheckAnswerExist(student, answer, idx, score))    // the item exists, this is an update
            {
                session << Poco::format("UPDATE %s SET file='%s',score=%f WHERE idx='%s'",
                    ms_answerTables[a.Type()], 
                    a.answerFile.GetEncodedString(),
                    a.answerScore, 
                    idx),
                    now;
            }
            else    // the item doesn't exists, this is an insertion
            {
                session << Poco::format("INSERT INTO %s VALUES(?, ?, ?, ?, ?)",
                    ms_answerTables[a.Type()]),
                    use(GenerateIndex(student.studentNumber, a.questionID)),
                    use(student.studentNumber),
                    use(a.questionID),
                    use(a.answerScore),
                    use(a.answerFile.GetEncodedString()),
                    now;
            }

            UpdateStudentScore(student, score, a.answerScore);
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::SavePropertyAnswer(const StudentInfo &student, CAnswer *answer)
    {
        try
        {
            Session &session = *m_session;
            CPropertyAnswer &a = *dynamic_cast<CPropertyAnswer *>(answer);
            std::string idx;
            double score = 0;
            std::string property;
            if (!a.answerProperties.empty())
            {
                property = CQuestionLibrary::AssembleString(a.answerProperties);
                property += CQuestionLibrary::separator;
                property += CQuestionLibrary::AssembleString(a.answerValues);
            }

            if (CheckAnswerExist(student, answer, idx, score))    // the item exists, this is an update
            {
                session << Poco::format("UPDATE %s SET file='%s',score=%f,answer='%s' WHERE idx='%s'",
                    ms_answerTables[a.Type()], 
                    a.answerFile.GetEncodedString(),
                    a.answerScore,
                    property,
                    idx),
                    now;
            }
            else    // the item doesn't exists, this is an insertion
            {
                session << Poco::format("INSERT INTO %s VALUES(?, ?, ?, ?, ?, ?)",
                    ms_answerTables[a.Type()]),
                    use(GenerateIndex(student.studentNumber, a.questionID)),
                    use(student.studentNumber),
                    use(a.questionID),
                    use(a.answerScore),
                    use(property),
                    use(a.answerFile.GetEncodedString()),
                    now;
            }

            UpdateStudentScore(student, score, a.answerScore);
            return true;
        }
        catch (Poco::Exception &exp)
        {
            systemLogger.error(exp.displayText());
            return false;
        }
    }

    bool CAnswerDatabase::IsInitialized(void) const
    {
        return m_flagInitialized;
    }

    void CAnswerDatabase::Finalize(void)
    {
        if (!m_flagInitialized)
            return;

        m_session->close();
        m_session = NULL;

        m_flagInitialized = false;
    }

}